package dao;

import model.Student;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class StudentDemo {

    public static void main(String[] args) throws Exception {
//        removeEmploy("62");
//        addStudent();
        queryStudent();
    }

    public static void contectOracle() {

    }

    public static void removeStudent(String pid) throws Exception {
// 1.在工程中导入mysql驱动jar包
        // 2.注册Driver驱动类  包名+类名
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 3.DriverManager 驱动管理类
        //通过驱动管理类建立和制定mysql服务器的连接，生成一个连接对象
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "user01", "123456");
        // 4.获取执行平台
        Statement statement = connection.createStatement();
        // 5.执行sql语句
        String str = "delete from studentbak where product.pid =" + pid;
        //影响数据库表的行数
        int i = statement.executeUpdate(str);//对于数据库来说，增删改都是更新操作 executeUpdate(sql)
        if (i < 1) {
            System.out.println("删除失败");
        } else {
            System.out.println("删除成功");
        }
        //遵从先打开后关闭
        statement.close();
        connection.close();
    }

    public static void queryStudent() throws Exception {
        // 1.在工程中导入mysql驱动jar包
        // 2.注册Driver驱动类  包名+类名
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 3.DriverManager 驱动管理类
        //通过驱动管理类建立和制定mysql服务器的连接，生成一个连接对象
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "user01", "123456");
        // 4.获取执行平台
        Statement statement = connection.createStatement();
//     5.执行sql语句

        String str = "select stuno,stuname,stuage,gid from studentbak where stuage=23 or gid=33";
        long startTime=System.currentTimeMillis();
        //set结果集  集合
        ResultSet resultSet = statement.executeQuery(str);
        long endTime=System.currentTimeMillis();
        long time=endTime-startTime;
        System.out.println("or查询时间为"+time);
        List<Student> list = new ArrayList<>();
        while (resultSet.next()) {  //判断是否有下一条记录值
            //从resultSet取出结果值
            String stuno = resultSet.getString("stuno");//columnTndex 列的索引
            String stuname = resultSet.getString("stuname");
            int stuage = resultSet.getInt("stuage");
            String gid = resultSet.getString("gid");
            list.add(new Student(stuno, stuname, stuage, gid));
        }

        System.out.println(list);
        resultSet.close();
        statement.close();
        connection.close();
    }

    public static void addStudent() throws Exception {
        // 1.在工程中导入mysql驱动jar包
        // 2.注册Driver驱动类  包名+类名
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // 3.DriverManager 驱动管理类
        //通过驱动管理类建立和制定mysql服务器的连接，生成一个连接对象
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "user01", "123456");
        // 4.获取执行平台
        Statement statement = connection.createStatement();
        // 5.执行sql语句
        long startTime=System.currentTimeMillis();
        for (int i = 1; i <= 10000; i++) {
            int j = i % 100;
            String str1 = "insert into studentbak values('" + i + "','飞飞'," + j + ",'"+j+"')";
            //set结果集  集合
          statement.execute(str1);
        }
        long endTime=System.currentTimeMillis();
        System.out.println("插入一万条数据花费时间"+(endTime-startTime)+"毫秒");


        statement.close();
        connection.close();
    }
}
